<?php
/**
* @file $Id: GetStuList.fnc.php 595 2007-07-12 21:23:38Z focus-sis $
* @package Focus/SIS
* @copyright Copyright (C) 2006 Andrew Schmadeke. All rights reserved.
* @license http://www.gnu.org/copyleft/gpl.html GNU/GPL, see LICENSE.txt
* Focus/SIS is free software. This version may have been modified pursuant
* to the GNU General Public License, and as distributed it includes or
* is derivative of works licensed under the GNU General Public License or
* other free or open source software licenses.
* See COPYRIGHT.txt for copyright notices and details.
*/

function GetStuList(& $extra)
{	global $view_other_RET,$_FOCUS;

	if(!$extra)
		$extra = array();

	if((!$extra['SELECT_ONLY'] || strpos($extra['SELECT_ONLY'],'GRADE_ID')!==false) && !$extra['functions']['GRADE_ID'])
		$functions = array('GRADE_ID'=>'GetGrade');
	else
		$functions = array();

	if($extra['functions'])
		$functions += $extra['functions'];
	
	if(!$extra['DATE'])
		$extra['DATE'] = DBDate();
	
	if($_REQUEST['expanded_view']=='true')
	{
		if(!$extra['columns_after'])
			$extra['columns_after'] = array();

		$view_fields_RET = DBGet(DBQuery("SELECT cf.ID,cf.TYPE,cf.TITLE FROM PROGRAM_USER_CONFIG puc,CUSTOM_FIELDS cf WHERE puc.TITLE=cf.ID AND puc.PROGRAM='StudentFieldsView' AND puc.USERNAME='".User('USERNAME')."' AND puc.VALUE='Y'"));
		$view_address_RET = DBGet(DBQuery("SELECT VALUE FROM PROGRAM_USER_CONFIG WHERE PROGRAM='StudentFieldsView' AND TITLE='ADDRESS' AND USERNAME='".User('USERNAME')."'"));
		$view_address_RET = $view_address_RET[1]['VALUE'];
		$view_other_RET = DBGet(DBQuery("SELECT TITLE,VALUE FROM PROGRAM_USER_CONFIG WHERE PROGRAM='StudentFieldsView' AND TITLE IN ('CONTACT_INFO','HOME_PHONE','GUARDIANS','ALL_CONTACTS') AND USERNAME='".User('USERNAME')."'"),array(),array('TITLE'));

		if(!count($view_fields_RET) && !isset($view_address_RET) && !isset($view_other_RET['CONTACT_INFO']))
		{
			$extra['columns_after'] = array('CONTACT_INFO'=>'<IMG SRC=assets/down_phone_button.gif border=0>','CUSTOM_200000000'=>'Gender','CUSTOM_200000001'=>'Ethnicity','ADDRESS'=>'Mailing Address','CITY'=>'City','STATE'=>'State','ZIPCODE'=>'Zipcode') + $extra['columns_after'];
			$select = ',s.STUDENT_ID AS CONTACT_INFO,s.CUSTOM_200000000,s.CUSTOM_200000001,COALESCE(a.MAIL_ADDRESS,a.ADDRESS) AS ADDRESS,COALESCE(a.MAIL_CITY,a.CITY) AS CITY,COALESCE(a.MAIL_STATE,a.STATE) AS STATE,COALESCE(a.MAIL_ZIPCODE,a.ZIPCODE) AS ZIPCODE ';
			$extra['FROM'] = " LEFT OUTER JOIN STUDENTS_JOIN_ADDRESS sam ON (ssm.STUDENT_ID=sam.STUDENT_ID AND sam.MAILING='Y') LEFT OUTER JOIN ADDRESS a ON (sam.ADDRESS_ID=a.ADDRESS_ID) ".$extra['FROM'];
			$functions['CONTACT_INFO'] = 'makeContactInfo';
			$extra['singular'] = _('Student Address');
			$extra['plural'] = _('Student Addresses');

			$extra2['NoSearchTerms'] = true;
			$extra2['addr'] = false;
			// LO_index is superfolous here ... plus, it adds extra data to the array which throws off makeContactInfo
			$extra2['LO_index'] = false;
			$extra2['SELECT_ONLY'] = 'ssm.STUDENT_ID,p.PERSON_ID,p.FIRST_NAME,p.LAST_NAME,sjp.STUDENT_RELATION,pjc.TITLE,pjc.VALUE,addr.PHONE,sjp.ADDRESS_ID,COALESCE(sjp.CUSTODY,\'N\')  ';
			$extra2['FROM'] .= ',ADDRESS addr,STUDENTS_JOIN_ADDRESS sja LEFT OUTER JOIN STUDENTS_JOIN_PEOPLE sjp ON (sja.STUDENT_ID=sjp.STUDENT_ID AND sja.ADDRESS_ID=sjp.ADDRESS_ID AND (sjp.CUSTODY=\'Y\' OR sjp.EMERGENCY=\'Y\')) LEFT OUTER JOIN PEOPLE p ON (p.PERSON_ID=sjp.PERSON_ID) LEFT OUTER JOIN PEOPLE_JOIN_CONTACTS pjc ON (pjc.PERSON_ID=p.PERSON_ID) ';
			$extra2['WHERE'] .= ' AND addr.ADDRESS_ID=sja.ADDRESS_ID AND sja.STUDENT_ID=ssm.STUDENT_ID ';
			if($extra2['ORDER_BY'])
				$extra2['ORDER_BY'] .= ',';
			$extra2['ORDER_BY'] .= 'COALESCE(sjp.CUSTODY,\'N\') DESC';
			$extra2['group'] = array('STUDENT_ID','PERSON_ID');
		
			// EXPANDED VIEW BREAKS THIS QUERY ... SO, TURN IT OFF
			if(!$_REQUEST['_FOCUS_PDF'])
			{
				$expanded_view = $_REQUEST['expanded_view'];
				$_REQUEST['expanded_view'] = false;
				$search_terms = $_FOCUS['SearchTerms'];
				$_FOCUS['makeContactInfo'] = GetStuList($extra2);
				$_REQUEST['expanded_view'] = $expanded_view;
				$_FOCUS['SearchTerms'] = $search_terms;
			}
			else
				unset($extra2['columns_after']['CONTACT_INFO']);
		}
		else
		{
			if($view_other_RET['CONTACT_INFO'][1]['VALUE']=='Y' && !$_REQUEST['_FOCUS_PDF'])
			{
				$select .= ',NULL AS CONTACT_INFO ';
				$extra['columns_after']['CONTACT_INFO'] = '<IMG SRC=assets/down_phone_button.gif border=0>';
				$functions['CONTACT_INFO'] = 'makeContactInfo';

				$extra2 = $extra;
				$extra2['NoSearchTerms'] = true;
				$extra2['addr'] = false;
				// LO_index is superfolous here ... plus, it adds extra data to the array which throws off makeContactInfo
				$extra2['LO_index'] = false;
				$extra2['SELECT'] = '';
				$extra2['SELECT_ONLY'] = 'ssm.STUDENT_ID,p.PERSON_ID,p.FIRST_NAME,p.LAST_NAME,sjp.STUDENT_RELATION,pjc.TITLE,pjc.VALUE,addr.PHONE,sjp.ADDRESS_ID,COALESCE(sjp.CUSTODY,\'N\') ';
				$extra2['FROM'] .= ',ADDRESS addr,STUDENTS_JOIN_ADDRESS sja LEFT OUTER JOIN STUDENTS_JOIN_PEOPLE sjp ON (sja.STUDENT_ID=sjp.STUDENT_ID AND sja.ADDRESS_ID=sjp.ADDRESS_ID AND (sjp.CUSTODY=\'Y\' OR sjp.EMERGENCY=\'Y\')) LEFT OUTER JOIN PEOPLE p ON (p.PERSON_ID=sjp.PERSON_ID) LEFT OUTER JOIN PEOPLE_JOIN_CONTACTS pjc ON (pjc.PERSON_ID=p.PERSON_ID) ';
				$extra2['WHERE'] .= ' AND addr.ADDRESS_ID=sja.ADDRESS_ID AND sja.STUDENT_ID=ssm.STUDENT_ID ';
				if($extra2['ORDER_BY'])
					$extra2['ORDER_BY'] .= ',';
				$extra2['ORDER_BY'] .= 'COALESCE(sjp.CUSTODY,\'N\') DESC';
				$extra2['group'] = array('STUDENT_ID','PERSON_ID');
				$extra2['functions'] = array();
				$extra2['link'] = array();
			
				// EXPANDED VIEW BREAKS THIS QUERY ... SO, TURN IT OFF
				$expanded_view = $_REQUEST['expanded_view'];
				$_REQUEST['expanded_view'] = false;
				$search_terms = $_FOCUS['SearchTerms'];
				$_FOCUS['makeContactInfo'] = GetStuList($extra2);
				$_REQUEST['expanded_view'] = $expanded_view;
				$_FOCUS['SearchTerms'] = $search_terms;
			}
			foreach($view_fields_RET as $field)
			{
				$extra['columns_after']['CUSTOM_'.$field['ID']] = $field['TITLE'];
				if($field['TYPE']=='date')
					$functions['CUSTOM_'.$field['ID']] = 'ProperDate';
				if($field['TYPE']=='numeric')
					$functions['CUSTOM_'.$field['ID']] = 'removeDot00';
				$select .= ',s.CUSTOM_'.$field['ID'];
			}
			if($view_address_RET)
			{
				$extra['FROM'] = " LEFT OUTER JOIN STUDENTS_JOIN_ADDRESS sam ON (ssm.STUDENT_ID=sam.STUDENT_ID AND sam.".$view_address_RET."='Y') LEFT OUTER JOIN ADDRESS a ON (sam.ADDRESS_ID=a.ADDRESS_ID) ".$extra['FROM'];
				$extra['columns_after'] += array('ADDRESS'=>ucwords(strtolower(str_replace('_',' ',$view_address_RET))).' '._('Address'),'CITY'=>_('City'),'STATE'=>_('State'),'ZIPCODE'=>_('Zipcode'));
				if($view_address_RET!='MAILING')
					$select .= ",a.ADDRESS_ID,a.ADDRESS,a.CITY,a.STATE,a.ZIPCODE,a.PHONE,ssm.STUDENT_ID AS PARENTS";
				else
					$select .= ",a.ADDRESS_ID,COALESCE(a.MAIL_ADDRESS,a.ADDRESS) AS ADDRESS,COALESCE(a.MAIL_CITY,a.CITY) AS CITY,COALESCE(a.MAIL_STATE,a.STATE) AS STATE,COALESCE(a.MAIL_ZIPCODE,a.ZIPCODE) AS ZIPCODE,a.PHONE,ssm.STUDENT_ID AS PARENTS ";
				$extra['singular'] = _('Student Address');
				$extra['plural'] = _('Student Addresses');
				
				if($view_other_RET['HOME_PHONE'][1]['VALUE']=='Y')
				{
					$functions['PHONE'] = 'makePhone';
					$extra['columns_after']['PHONE'] = _('Home Phone');
				}
				if($view_other_RET['GUARDIANS'][1]['VALUE']=='Y' || $view_other_RET['ALL_CONTACTS'][1]['VALUE']=='Y')
				{
					$functions['PARENTS'] = 'makeParents';
					if($view_other_RET['ALL_CONTACTS'][1]['VALUE']=='Y')
						$extra['columns_after']['PARENTS'] = _('Contacts');
					else
						$extra['columns_after']['PARENTS'] = _('Guardians');												
				}
			}
		}
		$extra['SELECT'] .= $select;
	}
	elseif($_REQUEST['addr'] || $extra['addr'])
	{
		$extra['FROM'] = " LEFT OUTER JOIN STUDENTS_JOIN_ADDRESS sam ON (ssm.STUDENT_ID=sam.STUDENT_ID ".$extra['STUDENTS_JOIN_ADDRESS'].") LEFT OUTER JOIN ADDRESS a ON (sam.ADDRESS_ID=a.ADDRESS_ID) ".$extra['FROM'];
		$distinct = 'DISTINCT ';
	}

	if(Preferences('NAME_FORMAT')=='Y')
		$full_name = "s.LAST_NAME||', '||s.FIRST_NAME||' '||COALESCE(s.MIDDLE_NAME,' ')";
	else
		$full_name = "s.FIRST_NAME||' '||COALESCE(s.MIDDLE_NAME,' ')||' '||s.LAST_NAME";
	switch(User('PROFILE'))
	{
		case 'admin':
//			if($_REQUEST['include_inactive']=='Y')
//				$distinct = 'DISTINCT ';
			//$sql = 'SELECT '.$distinct;
			$sql = 'SELECT ';

			if($extra['SELECT_ONLY'])
				$sql .= $extra['SELECT_ONLY'];
			else
			{
				$sql .= $full_name." AS FULL_NAME,s.LAST_NAME,s.FIRST_NAME,s.MIDDLE_NAME,s.STUDENT_ID,ssm.SCHOOL_ID,ssm.SCHOOL_ID AS LIST_SCHOOL_ID,ssm.GRADE_ID ".$extra['SELECT'];
				if($_REQUEST['include_inactive']=='Y')
					$sql .= ','.db_case(array("('".DBDate()."' BETWEEN ssm.START_DATE AND ssm.END_DATE OR ssm.END_DATE IS NULL)",'true',"'Active'","'Inactive'")).' AS ACTIVE';
			}
			$sql .= " FROM STUDENTS s,STUDENT_ENROLLMENT ssm ".$extra['FROM']." WHERE ssm.STUDENT_ID=s.STUDENT_ID ";
			if(UserSchool() && $_REQUEST['_search_all_schools']!='Y')
				$sql .= " AND ssm.SCHOOL_ID='".UserSchool()."' ";
			else
			{
				$schools = substr(str_replace(",","','",User('SCHOOLS')),2,-2);
				if($schools)
					$sql .= " AND ssm.SCHOOL_ID IN ($schools) ";
				$extra['columns_after']['LIST_SCHOOL_ID'] = _('School');
				$functions['LIST_SCHOOL_ID'] = 'GetSchool';
			}
					
			if($_REQUEST['include_inactive']!='Y')
				$sql .=	" AND ssm.SYEAR='".UserSyear()."' AND ('".$extra['DATE']."' BETWEEN ssm.START_DATE AND ssm.END_DATE OR (ssm.END_DATE IS NULL)) ";
			else
			{
				$sql .= " AND ssm.ID=(SELECT ID FROM STUDENT_ENROLLMENT WHERE STUDENT_ID=ssm.STUDENT_ID AND SYEAR<='".UserSyear()."' ORDER BY START_DATE DESC LIMIT 1) ";
				$extra['columns_after']['ACTIVE'] = _('Status');
			}
			//$sql .= " AND ssm.SYEAR='".UserSyear()."' AND ('".DBDate()."' BETWEEN ssm.START_DATE AND ssm.END_DATE OR ssm.END_DATE IS NULL)";
		break;
		
		case 'teacher':
			//$sql = 'SELECT '.$distinct;
			$sql = 'SELECT ';
			if($extra['SELECT_ONLY'])
				$sql .= $extra['SELECT_ONLY'];
			else
			{
				$sql .= ' '.$full_name." AS FULL_NAME,s.STUDENT_ID,ssm.SCHOOL_ID,ssm.GRADE_ID ".$extra['SELECT'];
				if($_REQUEST['include_inactive']=='Y')
				{
					$sql .= ','.db_case(array("(ssm.SYEAR='".UserSyear()."' AND ('".$extra['DATE']."' BETWEEN ssm.START_DATE AND ssm.END_DATE OR ssm.END_DATE IS NULL AND '".$extra['DATE']."'>=ssm.START_DATE))",'true',"'<FONT color=green>Active</FONT>'","'<FONT color=red>Inactive</FONT>'")).' AS ACTIVE';
					$sql .= ','.db_case(array("(ss.SYEAR='".UserSyear()."' AND ('".$extra['DATE']."' BETWEEN ss.START_DATE AND ss.END_DATE OR ss.END_DATE IS NULL AND '".$extra['DATE']."'>=ss.START_DATE))",'true',"'<FONT color=green>Active</FONT>'","'<FONT color=red>Inactive</FONT>'")).' AS ACTIVE_SCHEDULE';
				}
			}
			$sql .= " FROM STUDENTS s,COURSE_PERIODS cp,SCHEDULE ss,STUDENT_ENROLLMENT ssm ".$extra['FROM']."
					WHERE ssm.SCHOOL_ID='".UserSchool()."' AND ssm.SYEAR='".UserSyear()."' AND ssm.SYEAR=cp.SYEAR AND ssm.SYEAR=ss.SYEAR ";
			if(!$extra['mp_list'])
				$sql .= " AND ss.MARKING_PERIOD_ID IN (".GetAllMP('QTR',UserMP()).") ";
			else
				$sql .= " AND ss.MARKING_PERIOD_ID IN (".$extra['mp_list'].") ";
				$sql .=	" AND ssm.STUDENT_ID=s.STUDENT_ID AND s.STUDENT_ID=ss.STUDENT_ID AND ss.STUDENT_ID = ssm.STUDENT_ID
						AND cp.TEACHER_ID='".User('STAFF_ID')."'
						AND cp.COURSE_ID=ss.COURSE_ID AND cp.COURSE_WEIGHT=ss.COURSE_WEIGHT AND cp.COURSE_PERIOD_ID=ss.COURSE_PERIOD_ID 
						AND ('".$extra['DATE']."' BETWEEN ss.START_DATE AND ss.END_DATE OR (ss.END_DATE IS NULL AND '".$extra['DATE']."'>=ss.START_DATE))";
			if($_REQUEST['_search_all_periods']!='Y')
				$sql .= " AND cp.COURSE_PERIOD_ID='".UserCoursePeriod()."' ";

			if($_REQUEST['include_inactive']!='Y')
			{
				$sql .= " AND ('".$extra['DATE']."' BETWEEN ss.START_DATE AND ss.END_DATE OR ss.END_DATE IS NULL AND '".$extra['DATE']."'>=ss.START_DATE)";
				$sql .= " AND ('".$extra['DATE']."' BETWEEN ssm.START_DATE AND ssm.END_DATE OR ssm.END_DATE IS NULL AND '".$extra['DATE']."'>=ssm.START_DATE)";
			}
			else
				$sql .= " AND ssm.ID=(SELECT ID FROM STUDENT_ENROLLMENT WHERE STUDENT_ID=ssm.STUDENT_ID AND SYEAR=ssm.SYEAR ORDER BY START_DATE DESC LIMIT 1)";

			if(!$extra['SELECT_ONLY'] && $_REQUEST['include_inactive']=='Y')
			{
				$extra['columns_after']['ACTIVE'] = _('School Status');
				$extra['columns_after']['ACTIVE_SCHEDULE'] = _('Course Status');
			}
		break;
		
		case 'parent':
		case 'student':
			$sql = 'SELECT ';
			if($extra['SELECT_ONLY'])
				$sql .= $extra['SELECT_ONLY'];
			else
				$sql .= ' '.$full_name." AS FULL_NAME,s.STUDENT_ID,ssm.SCHOOL_ID,ssm.GRADE_ID ".$extra['SELECT'];
			$sql .= " FROM STUDENTS s,STUDENT_ENROLLMENT ssm ".$extra['FROM']."
					WHERE ssm.STUDENT_ID=s.STUDENT_ID AND ssm.SYEAR='".UserSyear()."' AND ssm.SCHOOL_ID='".UserSchool()."' AND ('".DBDate()."' BETWEEN ssm.START_DATE AND ssm.END_DATE OR (ssm.END_DATE IS NULL AND '".DBDate()."'>ssm.START_DATE)) AND ssm.STUDENT_ID='".UserStudentID()."'";
		break;
	}

	$sql = appendSQL($sql,$extra);
	
	$sql .= $extra['WHERE'].' ';
	$sql .= CustomFields('where');

	if($extra['GROUP'])
		$sql .= ' GROUP BY '.$extra['GROUP'];

	if(!$extra['ORDER_BY'] && !$extra['SELECT_ONLY'])
	{
		if(Preferences('SORT')=='Enrolled')
		{
			if(User('PROFILE')=='teacher')
				$sql .= " ORDER BY ss.START_DATE,LAST_NAME,FIRST_NAME";
			else
				$sql .= " ORDER BY ssm.START_DATE,LAST_NAME,FIRST_NAME";
		}
		elseif(Preferences('SORT')=='Grade')
			$sql .= " ORDER BY GRADE_ID,LAST_NAME,FIRST_NAME";
		else
			$sql .= " ORDER BY LAST_NAME,FIRST_NAME";
		$sql .= $extra['ORDER'];
	}
	elseif($extra['ORDER_BY'])
		$sql .= ' ORDER BY '.$extra['ORDER_BY'];
	
	if($extra['DEBUG']===true)
		echo '<!--'.$sql.'-->';

	$RET = DBGet(DBQuery($sql),$functions,$extra['group']);

	if($extra['LO_index']!==false && ($_REQUEST['modname']=='Students/Search.php' || $_REQUEST['modname']=='Students/Student.php' || $_REQUEST['modname']=='Scheduling/Schedule.php' || $_REQUEST['modname']=='Scheduling/Requests.php' || $_REQUEST['modname']=='Grades/StudentRCGrades.php' || $_REQUEST['modname']=='Attendance/StudentSummary.php' || $_REQUEST['modname']=='Eligibility/Student.php' || $_REQUEST['modname']=='Students/Student.php?include=Comments'))
	{
		if($_REQUEST['LO_sort'])
		{
			$result_count = count($RET);
			foreach($RET as $sort)
			{	
				if(substr($sort[$_REQUEST['LO_sort']],0,4)!='<!--')
					$sort_array[] = $sort[$_REQUEST['LO_sort']];
				else
					$sort_array[] = substr($sort[$_REQUEST['LO_sort']],4,strpos($sort[$_REQUEST['LO_sort']],'-->')-5);
			}
			if($_REQUEST['LO_direction']==-1)
				$dir = SORT_DESC;
			else
				$dir = SORT_ASC;
			
			if($result_count>1)	
			{
				if(is_int($sort_array[1]) || is_double($sort_array[1]))	
					array_multisort($sort_array,$dir,SORT_NUMERIC,$RET); 		
				else
					array_multisort($sort_array,$dir,$RET);
				for($i=$result_count-1;$i>=0;$i--)
					$RET[$i+1] = $RET[$i];
				unset($RET[0]);
			}
			$_FOCUS['LO_sorted'] = true;
		}

		$i = 0;
		$_SESSION['student_list'] = array();
		foreach($RET as $index=>$student)
		{
			$i++;
			$_SESSION['student_list'][] = array('student_id'=>$student['STUDENT_ID'],'school_id'=>$student['SCHOOL_ID']);
			$RET[$index]['index'] = $i;
		}
	}
	return $RET;
}

function makeContactInfo($student_id,$column)
{	global $THIS_RET,$_FOCUS;

	$contacts_RET = $_FOCUS['makeContactInfo'];
	// this first if takes care of the situation where a student has only an address with no contacts & no phone num
	if(count($contacts_RET[$THIS_RET['STUDENT_ID']])==1 && $contacts_RET[$THIS_RET['STUDENT_ID']][key($contacts_RET[$THIS_RET['STUDENT_ID']])][1]['COALESCE']=='N' && $contacts_RET[$THIS_RET['STUDENT_ID']][key($contacts_RET[$THIS_RET['STUDENT_ID']])][1]['PHONE']=='')
		$tipmessage = _('This student has no contact information.');
	elseif(count($contacts_RET[$THIS_RET['STUDENT_ID']]))
	{
		foreach($contacts_RET[$THIS_RET['STUDENT_ID']] as $person)
		{
			if($_REQUEST['_FOCUS_PDF'])
			{
				if($person[1]['FIRST_NAME'] || $person[1]['LAST_NAME'])
					$tipmessage .= '('.$person[1]['STUDENT_RELATION'].': '.$person[1]['FIRST_NAME'].' '.$person[1]['LAST_NAME'].') ';
				if($person[1]['PHONE'])
					$tipmessage .= _('Home Phone').': '.$person[1]['PHONE'].'; ';
				foreach($person as $info)
				{
					if($info['TITLE'] || $info['VALUE'])
						$tipmessage .= $info['TITLE'].': '.$info['VALUE'].'; ';
				}
			}
			else
			{
				if($person[1]['FIRST_NAME'] || $person[1]['LAST_NAME'])
					$tipmessage .= '<B>'.$person[1]['STUDENT_RELATION'].': '.$person[1]['FIRST_NAME'].' '.$person[1]['LAST_NAME'].'</B><BR>';
				$tipmessage .= '<TABLE>';
				if($person[1]['PHONE'])
					$tipmessage .= '<TR><TD align='.ALIGN_RIGHT.'><font color=gray size=1 face=Verdana,Arial,Helvetica>'._('Home Phone').'</font> </TD><TD><font size=1 face=Verdana,Arial,Helvetica>'.$person[1]['PHONE'].'</font></TD></TR>';
				foreach($person as $info)
				{
					if($info['TITLE'] || $info['VALUE'])
						$tipmessage .= '<TR><TD align='.ALIGN_RIGHT.'><font color=gray size=1 face=Verdana,Arial,Helvetica>'.$info['TITLE'].'</font></TD><TD><font size=1 face=Verdana,Arial,Helvetica>'.$info['VALUE'].'</font></TD></TR>';
				}
				$tipmessage .= '</TABLE>';
			}
		}
	}
	else
		$tipmessage = _('This student has no contact information.');

	if($_REQUEST['_FOCUS_PDF'])
		return $tipmessage;
	else
		return button('phone','','# onMouseOver=\'stm(["'._('Contact Information').'","'.str_replace("'","&rsquo;",$tipmessage).'"],["white","#333366","","","",,"black","#e8e8ff","","","",,,,2,"#333366",2,,,,,"",,,,]);\' onMouseOut=\'htm()\'');
}

function removeDot00($value,$column)
{
	return str_replace('.00','',$value);	
}

function makePhone($phone,$column='')
{	global $THIS_RET;
	
	if(strlen($phone)==10)
		$return .= '('.substr($phone,0,3).')'.substr($phone,3,7).'-'.substr($phone,7);
	if(strlen($phone)=='7')
		$return .= substr($phone,0,3).'-'.substr($phone,3);
	else
		$return .= $phone;
		
	return $return;
}

function makeParents($student_id,$column='')
{	global $THIS_RET,$view_other_RET,$_FOCUS;

	if($THIS_RET['PARENTS']==$student_id)
	{
		if(!$THIS_RET['ADDRESS_ID'])
			$THIS_RET['ADDRESS_ID'] = 0;

		$THIS_RET['PARENTS'] = '';

		if($_FOCUS['makeParents'])
			$constraint = 'AND (LOWER(sjp.STUDENT_RELATION) LIKE \''.strtolower($_FOCUS['makeParents']).'%\')';
		elseif($view_other_RET['ALL_CONTACTS'][1]['VALUE']=='Y')
			$constraint = "AND (sjp.CUSTODY='Y' OR sjp.EMERGENCY='Y')";
		else
			$constraint = "AND sjp.CUSTODY='Y'";

		$people_RET = DBGet(DBQuery("SELECT p.PERSON_ID,p.FIRST_NAME,p.LAST_NAME,sjp.ADDRESS_ID,sjp.CUSTODY,sjp.EMERGENCY FROM STUDENTS_JOIN_PEOPLE sjp,PEOPLE p WHERE sjp.PERSON_ID=p.PERSON_ID AND sjp.STUDENT_ID='$student_id' ".$constraint." ORDER BY p.LAST_NAME,p.FIRST_NAME"));
		if(count($people_RET))
		{
			foreach($people_RET as $person)
			{
				if($person['ADDRESS_ID']==$THIS_RET['ADDRESS_ID'])
				{
					if($person['CUSTODY']=='Y')
						$color = '0000FF';
					elseif($person['EMERGENCY']=='Y')
						$color = 'FFFF00';

					$THIS_RET['PARENTS'] .= '<TR><TD>'.button('dot',$color,'',6).'</TD><TD><A HREF=# onclick=\'window.open("Modules.php?modname=misc/ViewContact.php?person_id='.$person['PERSON_ID'].'&include_top=false","","scrollbars=yes,resizable=yes,width=400,height=200");\'>'.$person['FIRST_NAME'].' '.$person['LAST_NAME'].'</TR>';
					if($_REQUEST['_FOCUS_PDF'])
						$THIS_RET['PARENTS'] .= ', ';
				}
			}
			if($_REQUEST['_FOCUS_PDF'])
				$THIS_RET['PARENTS'] = substr($THIS_RET['PARENTS'],0,-2);
		}
	}
	
	if($THIS_RET['PARENTS'])
		return '<TABLE border=0 cellpadding=0 cellspacing=0 class=innerLO_field>'.$THIS_RET['PARENTS'].'</TABLE>';
}

function appendSQL($sql,$extra='')
{	global $_FOCUS;

	if($_REQUEST['stuid'])
	{
		$sql .= " AND ssm.STUDENT_ID = '$_REQUEST[stuid]' ";
		if(!$extra['NoSearchTerms'])
			$_FOCUS['SearchTerms'] .= '<font color=gray><b>'._('Student ID').': </b></font>'.$_REQUEST['stuid'].'<BR>';
	}
	if($_REQUEST['last'])
	{
		$sql .= " AND LOWER(s.LAST_NAME) LIKE '".strtolower($_REQUEST['last'])."%' ";
		if(!$extra['NoSearchTerms'])
			$_FOCUS['SearchTerms'] .= '<font color=gray><b>'._('Last Name starts with').': </b></font>'.$_REQUEST['last'].'<BR>';
	}
	if($_REQUEST['first'])
	{
		$sql .= " AND LOWER(s.FIRST_NAME) LIKE '".strtolower($_REQUEST['first'])."%' ";
		if(!$extra['NoSearchTerms'])
			$_FOCUS['SearchTerms'] .= '<font color=gray><b>'._('First Name starts with').': </b></font>'.$_REQUEST['first'].'<BR>';
	}
	if($_REQUEST['smart_name'])
	{
		if(is_numeric($_REQUEST['smart_name']))
		{
			$sql .= " AND ssm.STUDENT_ID='".$_REQUEST['smart_name']."'";
		}
		elseif(substr_count(trim($_REQUEST['smart_name']),' ')==00)
		{
			$smart_name = strtolower($_REQUEST['smart_name']);
			$sql .= " AND (LOWER(s.LAST_NAME) LIKE '".strtolower($smart_name)."%' OR LOWER(s.FIRST_NAME) LIKE '".strtolower($smart_name)."%')";
		}
		else
		{
			$smart_names = explode(' ',$_REQUEST['smart_name']);
			$sql .= ' AND ((';
			for($i=0;$i<count($smart_names)-1;$i++)
			{
				$sql .= "LOWER(s.FIRST_NAME) LIKE '".strtolower($smart_names[$i])."%' OR ";
			}
			$sql = substr($sql,0,-3).") AND LOWER(LAST_NAME) LIKE '".strtolower($smart_names[$i])."%')";
		}
			if(!$extra['NoSearchTerms'])
				$_FOCUS['SearchTerms'] .= '<font color=gray><b>'._('Student').': </b></font>'.$_REQUEST['smart_name'].'<BR>';
	}
	if($_REQUEST['grade'])
	{
		$sql .= "AND ssm.GRADE_ID = '$_REQUEST[grade]' ";	
		if(!$extra['NoSearchTerms'])
			$_FOCUS['SearchTerms'] .= '<font color=gray><b>'._('Grade').': </b></font>'.GetGrade($_REQUEST['grade']).'<BR>';
	}
	if($_REQUEST['calendar_id'])
	{
		$sql .= "AND ssm.CALENDAR_ID = '$_REQUEST[calendar_id]' ";
		$title_RET = DBGet(DBQuery("SELECT TITLE FROM ATTENDANCE_CALENDARS WHERE CALENDAR_ID='".$_REQUEST['calendar_id']."'"));
		if(!$extra['NoSearchTerms'])
			$_FOCUS['SearchTerms'] .= '<font color=gray><b>'._('Calendar').': </b></font>'.$title_RET[1]['TITLE'].'<BR>';
	}
	if($_REQUEST['addr'])
	{
		if(strlen($_REQUEST['addr'])==13 && ereg("[0-9]{5}\.\.\.[0-9]{5}",$_REQUEST['addr'])!==false)
			$sql .= " AND a.ZIPCODE BETWEEN ".substr($_REQUEST['addr'],0,5).' AND '.substr($_REQUEST['addr'],8).' ';
		else
			$sql .= " AND (LOWER(a.ADDRESS) LIKE '%".strtolower($_REQUEST['addr'])."%' OR LOWER(a.CITY) LIKE '".strtolower($_REQUEST['addr'])."%' OR LOWER(a.STATE)='".strtolower($_REQUEST['addr'])."' OR ZIPCODE LIKE '".$_REQUEST['addr']."%')";
		if(!$extra['NoSearchTerms'])
			$_FOCUS['SearchTerms'] .= '<font color=gray><b>'._('Address contains').': </b></font>'.$_REQUEST['addr'].'<BR>';
	}

	return $sql;
}
?>